package com.example.studyenglish.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;

import com.example.studyenglish.activity.BookImport;
import com.example.studyenglish.pattern.BookList;
import com.example.studyenglish.pattern.Vocabulary;
import com.example.studyenglish.pattern.VocabularyList;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.StringTokenizer;


public class DataAccess {
	public static String bookID = ""; // 选定的书
	public static String listID; // 选定的List
	public static int NumOfAttention; // 生词表的词数
	public Context context;
	private SqlHelper helper;
	public static boolean ifContinue;

	public DataAccess(Context context) {
		helper = new SqlHelper();
		Cursor cursor = null;
		cursor = helper.Query(context, SqlHelper.ATTENTION_TABLE, null, null,
				null, null, null, null);
		if (cursor == null)
			NumOfAttention = 0;
		else if (cursor.getCount() == 0)
			NumOfAttention = 0;
		else if (cursor.moveToLast())
			NumOfAttention = Integer.parseInt(cursor.getString(0));

		cursor.close();
		this.context = context;

	}

	/*
	 * 初始化词库
	 * 
	 * 参数： bookname：词库名称 list：所有单词 numOfList:list的数量
	 */
	public boolean initBook(String bookname, ArrayList<Vocabulary> list,
                            String numOfList, String NewName) throws Exception {
		boolean success = false;
		String bookName = null;
		bookName = NewName;
		Cursor cursor = helper.Query(context, SqlHelper.BOOKLIST_TABLE, null,
				null, null, null, null, null);
		String bookID = "book1";
		if (cursor.moveToLast()) {
			StringTokenizer st = new StringTokenizer(cursor.getString(0),
					"book");
			bookID = "book" + (Integer.parseInt(st.nextToken()) + 1);
		}
		BookImport.result = bookID;
		cursor.close();
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
		String date = f.format(cal.getTime());
		ContentValues cv = new ContentValues();
		helper.CreateTable(context, bookID);
		for (int i = 0; i < list.size(); i++) {
			if (ifContinue) {
				cv.put("ID", list.get(i).getID());
				cv.put("SPELLING", list.get(i).getSpelling());
				cv.put("MEANNING", list.get(i).getMeanning());
				cv.put("PHONETIC_ALPHABET", list.get(i).getPhonetic_alphabet());
				cv.put("LIST", list.get(i).getList());
				helper.Insert(context, bookID, cv);
				Log.i("Inserting word:", list.get(i).getSpelling());
			} else
				return false;
		}
		VocabularyList wordList = new VocabularyList();
		f = new SimpleDateFormat("yyyy-MM-dd");

		for (int i = 0; i < Integer.parseInt(numOfList); i++) {
			if (ifContinue) {
				wordList.setBestScore("");
				wordList.setBookID(bookID);
				wordList.setLearned("0");
				wordList.setLearnedTime("");
				wordList.setList(String.valueOf(i + 1));
				wordList.setReview_times("0");
				wordList.setReviewTime("");
				wordList.setShouldReview("0");
				this.InsertIntoList(wordList);
			} else
				return false;

			//Log.i("Inserting list", wordList.getList());
		}
		DataAccess.bookID = bookID;
		cv.clear();
		cv.put("ID", bookID);
		cv.put("NAME", bookName);
		cv.put("GENERATE_TIME", date);
		cv.put("NUMOFLIST", numOfList);
		cv.put("NUMOFWORD", list.size());
		helper.Insert(context, SqlHelper.BOOKLIST_TABLE, cv);
		Log.i("select", DataAccess.bookID);
		success = true;
		return success;
	}

	/*
	 * 查询词库参数selection：查询条件（where语句）selectionArgs:where中包含的“？”
	 */
	public ArrayList<BookList> QueryBook(String selection,
										 String[] selectionArgs) {

		Cursor cursor = helper.Query(context, SqlHelper.BOOKLIST_TABLE, null,
				selection, selectionArgs, null, null, null);
		ArrayList<BookList> list = new ArrayList<BookList>();
		;
		if (cursor.moveToFirst()) {
			do {
				BookList bl = new BookList();
				bl.setID(cursor.getString(0));
				bl.setName(cursor.getString(1));
				bl.setGenerateTime(cursor.getString(2));
				bl.setNumOfList(cursor.getString(3));
				bl.setNumOfWord(cursor.getInt(4));
				list.add(bl);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return list;

	}

	/*
	 * 查询单词 参数selection：查询条件（where语句）selectionArgs:where中包含的“？”
	 */
	public ArrayList<Vocabulary> QueryWord(String selection,
                                           String[] selectionArgs) {
		Cursor cursor = helper.Query(context, bookID, null, selection,
				selectionArgs, null, null, null);
		ArrayList<Vocabulary> list = new ArrayList<Vocabulary>();
		if (cursor.moveToFirst()) {

			do {
				Vocabulary vocabulary = new Vocabulary();
				vocabulary.setID(cursor.getString(0));
				vocabulary.setSpelling(cursor.getString(1));
				vocabulary.setMeanning(cursor.getString(2));
				vocabulary.setPhonetic_alphabet(cursor.getString(3));
				vocabulary.setList(cursor.getString(4));
				list.add(vocabulary);
				// Log.i("Querying word:",word.getSpelling());
			} while (cursor.moveToNext());
		}
		cursor.close();
		return list;

	}

	/*
	 * 查询list完成情况 参数 selection：查询条件（where语句）selectionArgs:where中包含的“？”
	 */
	public ArrayList<VocabularyList> QueryList(String selection,
                                               String[] selectionArgs) {
		Cursor cursor = helper.Query(context, SqlHelper.WORDLIST_TABLE, null,
				selection, selectionArgs, null, null, null);
		ArrayList<VocabularyList> list = new ArrayList<VocabularyList>();
		if (cursor.moveToFirst()) {

			do {
				VocabularyList vl = new VocabularyList();
				vl.setBookID(cursor.getString(0));
				vl.setList(cursor.getString(1));
				vl.setLearned(cursor.getString(2));
				vl.setLearnedTime(cursor.getString(3));
				vl.setReview_times(cursor.getString(4));
				vl.setReviewTime(cursor.getString(5));
				vl.setBestScore(cursor.getString(6));
				vl.setShouldReview(cursor.getString(7));
				list.add(vl);
			} while (cursor.moveToNext());
		}
		cursor.close();

		return list;

	}

	/*
	 * 更新list完成情况 参数 list：要进行更新的list（新数据）
	 */
	public void UpdateList(VocabularyList list) {
		ContentValues cv = new ContentValues();
		cv.put("BOOKID", list.getBookID());
		cv.put("LIST", list.getList());
		cv.put("LEARNED", list.getLearned());
		cv.put("LEARN_TIME", list.getLearnedTime());
		cv.put("REVIEW_TIMES", list.getReview_times());
		cv.put("REVIEWTIME", list.getReviewTime());
		cv.put("BESTSCORE", list.getBestScore());
		cv.put("SHOULDREVIEW", list.getShouldReview());

		helper.Update(context, SqlHelper.WORDLIST_TABLE, cv, " BOOKID ='"
				+ list.getBookID() + "'AND LIST ='" + list.getList() + "'",
				null);
	}

	/*
	 * 删除list完成情况 参数 list：要删除的list
	 */
	public void DeleteBook() {
		helper.Delete(context, SqlHelper.WORDLIST_TABLE, "BOOKID ='"
				+ DataAccess.bookID + "'", null);
		helper.Delete(context, SqlHelper.BOOKLIST_TABLE, "ID ='"
				+ DataAccess.bookID + "'", null);
		helper.DeleteTable(context, DataAccess.bookID);
	}

	/*
	 * 加入生词本 参数 word：要添加的单词
	 */
	public void InsertIntoAttention(Vocabulary vocabulary) {
		ContentValues cv = new ContentValues();
		cv.put("ID", String.valueOf(DataAccess.NumOfAttention + 1));
		cv.put("SPELLING", vocabulary.getSpelling());
		cv.put("MEANNING", vocabulary.getMeanning());
		cv.put("PHONETIC_ALPHABET", vocabulary.getPhonetic_alphabet());
		cv.put("LIST", "Attention");
		helper.Insert(context, SqlHelper.ATTENTION_TABLE, cv);

	}

	/*
	 * 删除生词本中的单词 参数 word：要添加的单词
	 */
	public void DeleteFromAttention(Vocabulary vocabulary) {
		helper.Delete(context, SqlHelper.ATTENTION_TABLE,
				"ID ='" + vocabulary.getID() + "'", null);
	}

	/*
	 * 查询生词本 selection：查询条件（where语句） selectionArgs:where中包含的“？”
	 */

	public ArrayList<Vocabulary> QueryAttention(String selection,
                                                String[] selectionArgs) {
		Cursor cursor = helper.Query(context, SqlHelper.ATTENTION_TABLE, null,
				selection, selectionArgs, null, null, null);
		ArrayList<Vocabulary> list = new ArrayList<Vocabulary>();
		if (cursor.moveToFirst()) {

			do {
				Vocabulary vocabulary = new Vocabulary();
				vocabulary.setID(cursor.getString(0));
				vocabulary.setSpelling(cursor.getString(1));
				vocabulary.setMeanning(cursor.getString(2));
				vocabulary.setPhonetic_alphabet(cursor.getString(3));
				vocabulary.setList(cursor.getString(4));
				list.add(vocabulary);
				// Log.i("Querying word:",word.getSpelling());
			} while (cursor.moveToNext());
		}
		cursor.close();
		return list;
	}

	/*
	 * 加入list完成情况 参数 list:要加入的list条目
	 */
	public void InsertIntoList(VocabularyList list) {
		ContentValues cv = new ContentValues();
		cv.put("BOOKID", list.getBookID());
		cv.put("LIST", list.getList());
		cv.put("LEARNED", list.getLearned());
		cv.put("LEARN_TIME", list.getLearnedTime());
		cv.put("REVIEW_TIMES", list.getReview_times());
		cv.put("REVIEWTIME", list.getReviewTime());
		cv.put("BESTSCORE", list.getBestScore());
		cv.put("SHOULDREVIEW", list.getShouldReview());
		helper.Insert(context, SqlHelper.WORDLIST_TABLE, cv);

	}

	public void ResetBook() {
		ArrayList<VocabularyList> list = new ArrayList<VocabularyList>();
		list = this.QueryList("BOOKID ='" + DataAccess.bookID + "'", null);
		for (int i = 0; i < list.size(); i++) {
			VocabularyList newlist = list.get(i);
			newlist.setBestScore("");
			newlist.setLearned("0");
			newlist.setLearnedTime("");
			newlist.setReview_times("0");
			newlist.setReviewTime("");
			newlist.setShouldReview("0");
			this.UpdateList(newlist);

		}
	}

	public void UpdateAttention(Vocabulary vocabulary) {
		ContentValues cv = new ContentValues();
		cv.put("ID", vocabulary.getID());
		cv.put("SPELLING", vocabulary.getSpelling());
		cv.put("MEANNING", vocabulary.getMeanning());
		cv.put("PHONETIC_ALPHABET", vocabulary.getPhonetic_alphabet());
		cv.put("LIST", vocabulary.getList());
		helper.Update(context, SqlHelper.ATTENTION_TABLE, cv, "ID ='"
				+ vocabulary.getID() + "'", null);
	}
}
